#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_exam1.py
# author:王雯雯
# datetime:2024-11-12 
# software: PyCharm

import pymysql
from..config.database_config import config


# 根据教师ID查询教师信息
def query_teacher_by_id(teacher_id):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "SELECT * FROM teachers WHERE teacher_id = %s"
        cursor.execute(sql, (teacher_id,))
        result = cursor.fetchone()
        if result:
            from..models.teacher_model import Teacher
            teacher = Teacher(*result)
            return teacher
        connection.close()
    except pymysql.Error as e:
        print(f"查询教师信息时出错: {e}")
    return None


# 增加教师信息
def insert_teacher(teacher):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "INSERT INTO teachers (teacher_id, name, subject) VALUES (%s, %s, %s)"
        cursor.execute(sql, (teacher.teacher_id, teacher.name, teacher.subject))
        connection.commit()
        connection.close()
        print("教师信息插入成功")
    except pymysql.Error as e:
        print(f"插入教师信息时出错: {e}")


# 根据教师ID删除教师信息
def delete_teacher_by_id(teacher_id):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "DELETE FROM teachers WHERE teacher_id = %s"
        cursor.execute(sql, (teacher_id,))
        connection.commit()
        connection.close()
        print("教师信息删除成功")
    except pymysql.Error as e:
        print(f"删除教师信息时出错: {e}")


# 根据教师姓名查询教师信息（简单示例，可优化）
def query_teachers_by_name(name):
    try:
        connection = pymysql.connect(**config)
        cursor = connection.cursor()
        sql = "SELECT * FROM teachers WHERE name LIKE %s"
        cursor.execute(sql, (f"%{name}%",))
        results = cursor.fetchall()
        teachers = []
        from..models.teacher_model import Teacher
        for result in results:
            teacher = Teacher(*result)
            teachers.append(teacher)
        connection.close()
        return teachers
    except pymysql.Error as e:
        print(f"查询教师信息时出错: {e}")
    return []